<?php
declare(strict_types=1);

namespace app\api\controller\v1;

use app\common\controller\Api;
use think\Db;
use think\Request;

class ProjectCourse extends Api
{
    protected $noNeedLogin = '*';
    protected $noNeedRight = '*';

    public function stats(Request $request)
    {
        $user = $this->auth->getUser(true);
        $uid = isset($user['uid']) ? $user['uid'] : intval($request->get('user_id', 0));
        $tableType = intval($request->get('table_type', 0));
        $schoolId = intval($request->get('school_id', 0));
        $projectId = intval($request->get('project_id', 0));
        $typeFilter = '';
        $projectFilter = '';
        $log = [
            'time' => date('Y-m-d H:i:s'),
            'uid' => $uid,
            'table_type' => $tableType,
            'school_id' => $schoolId,
            'project_id' => $projectId
        ];
        file_put_contents($_SERVER["DOCUMENT_ROOT"]."/../project_course_stats.log", json_encode($log)."\n", FILE_APPEND);

        if ($uid <= 0) {
            return json(['code' => 0, 'msg' => '缺少用户身份']);
        }


        $params = ['uid' => $uid];
        if ($schoolId > 0) { $params['school_id'] = $schoolId; }
        if ($projectId > 0) {
            if ($tableType === 1) {
                $typeFilter = ' AND t.table_type = 1 ';
                $params['table_id'] = $projectId;
                $projectFilter = " AND t.table_id = $projectId ";
            } else if ($tableType === 2) {
                $typeFilter = ' AND t.table_type = 2 ';
                $params['publicwelfare_id'] = $projectId;
                $projectFilter = " AND t.publicwelfare_id = $projectId ";
            }
        }

        $classesSql = "SELECT c.id as classes_id,c.name
                        FROM fa_course_table_autoplan t 
                        LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id 
                        LEFT JOIN eb_classes c ON c.id = t.classes_id 
                        LEFT JOIN eb_school s ON s.id = c.school_id 
                        WHERE t.status = 1 {$typeFilter} {$projectFilter}
                        GROUP BY c.id";

        try {
            $classRows = Db::query($classesSql);
        } catch (\Exception $e) {
            $classRows = [];
        }

        $classIds = [];
        foreach ($classRows as $r) {
            $classIds[] = intval($r['classes_id']);
        }

        if (empty($classIds)) {
            return json([
                'code' => 200,
                'msg' => 'success',
                'data' => [
                    'schools' => 0,
                    'classes' => 0,
                    'courses' => 0,
                    'students' => 0,
                    'attendances' => 0
                ]
            ]);
        }

        $idsStr = implode(',', array_map('intval', $classIds));

        $schoolsSql = "SELECT COUNT(DISTINCT s.id) AS total 
                        FROM fa_course_table_autoplan t 
                        LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id 
                        LEFT JOIN eb_classes c ON c.id = t.classes_id 
                        LEFT JOIN eb_school s ON s.id = c.school_id 
                        WHERE t.status = 1 {$typeFilter} {$projectFilter} AND t.classes_id IN ({$idsStr})";

        $classesSql2 = "SELECT COUNT(DISTINCT t.classes_id) AS total 
                         FROM fa_course_table_autoplan t 
                        LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id 
                        LEFT JOIN eb_classes c ON c.id = t.classes_id 
                        LEFT JOIN eb_school s ON s.id = c.school_id 
                         WHERE t.status = 1 {$typeFilter} {$projectFilter } AND t.classes_id IN ({$idsStr})";

        $coursesSql = "SELECT COUNT(*) AS total 
                        FROM fa_course_table_autoplan t 
                        LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id 
                        LEFT JOIN eb_classes c ON c.id = t.classes_id 
                        LEFT JOIN eb_school s ON s.id = c.school_id 
                        WHERE t.status = 1 {$typeFilter} {$projectFilter} AND t.classes_id IN ({$idsStr})";

        $studentsSql = "SELECT COUNT(DISTINCT f.user_id) AS total 
                         FROM eb_apply_form_item f 
                         WHERE f.status = 1 AND f.classes_id IN ({$idsStr})";

        $perClassCountsSql = "SELECT f.classes_id, COUNT(DISTINCT f.user_id) AS cnt 
                              FROM eb_apply_form_item f 
                              WHERE f.status = 1 AND f.classes_id IN ({$idsStr}) 
                              GROUP BY f.classes_id";

        $perClassCourseSql = "SELECT t.classes_id, COUNT(*) AS cnt 
                              FROM fa_course_table_autoplan t 
                              LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id 
                              LEFT JOIN eb_classes c ON c.id = t.classes_id 
                              LEFT JOIN eb_school s ON s.id = c.school_id 
                              WHERE t.status = 1 {$typeFilter} {$projectFilter} AND t.classes_id IN ({$idsStr}) 
                              GROUP BY t.classes_id";

        try {
            $schools = Db::query($schoolsSql);
            $classes = Db::query($classesSql2);
            $courses = Db::query($coursesSql);
            $students = Db::query($studentsSql);
            $perClassCounts = Db::query($perClassCountsSql);
            $perClassCourses = Db::query($perClassCourseSql);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '查询失败: '.$e->getMessage()]);
        }

        $courseTotal = isset($courses[0]['total']) ? intval($courses[0]['total']) : 0;
        $attendanceTotal = 0;
        $mapStudentCounts = [];
        foreach ($perClassCounts as $row) {
            $mapStudentCounts[intval($row['classes_id'])] = intval($row['cnt']);
        }
        $mapCourseCounts = [];
        foreach ($perClassCourses as $row) {
            $mapCourseCounts[intval($row['classes_id'])] = intval($row['cnt']);
        }
        foreach ($classIds as $cid) {
            $stuCnt = isset($mapStudentCounts[$cid]) ? $mapStudentCounts[$cid] : 0;
            $couCnt = isset($mapCourseCounts[$cid]) ? $mapCourseCounts[$cid] : 0;
            $attendanceTotal += ($stuCnt * $couCnt);
        }

        $data = [
            'schools' => isset($schools[0]['total']) ? intval($schools[0]['total']) : 0,
            'classes' => isset($classes[0]['total']) ? intval($classes[0]['total']) : 0,
            'courses' => $courseTotal,
            'students' => isset($students[0]['total']) ? intval($students[0]['total']) : 0,
            'attendances' => $attendanceTotal
        ];

        return json(['code' => 200, 'msg' => 'success', 'data' => $data]);
    }

    public function schoolOptions(Request $request)
    {
        $user = $this->auth->getUser(true);
        $uid = isset($user['uid']) ? $user['uid'] : intval($request->get('user_id', 0));
        $tableType = intval($request->get('table_type', 0));
        if ($uid <= 0) {
            return json(['code' => 0, 'msg' => '缺少用户身份']);
        }
        $typeFilter = '';
        if ($tableType === 1) {
            $typeFilter = ' AND t.table_type = 1 ';
        } else if ($tableType === 2) {
            $typeFilter = ' AND t.table_type = 2 ';
        }
        $sql = "SELECT DISTINCT s.id AS id, s.name AS name
                FROM fa_course_table_autoplan t
                LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id
                LEFT JOIN eb_classes c ON c.id = t.classes_id
                LEFT JOIN eb_school s ON s.id = c.school_id
                WHERE t.status = 1 {$typeFilter} AND :uid in (ct.teacher_id)
                ORDER BY name ASC";
        try {
            $rows = Db::query($sql, ['uid' => $uid]);
            $lastsql=Db::getLastSql();
            log_info($lastsql);
        } catch (\Exception $e) {
            $rows = [];
        }

        $options = [];
        foreach ($rows as $r) {
            $options[] = ['label' => (string)$r['name'], 'value' => intval($r['id'])];
        }
        return json(['code' => 200, 'msg' => 'success', 'data' => ['options' => $options]]);
    }

    public function projectOptions(Request $request)
    {
        $user = $this->auth->getUser(true);
        $uid = isset($user['uid']) ? $user['uid'] : intval($request->get('user_id', 0));
        $tableType = intval($request->get('table_type', 0));
        $schoolId = intval($request->get('school_id', 0));
        if ($uid <= 0) {
            return json(['code' => 0, 'msg' => '缺少用户身份']);
        }
        $typeFilter = '';
        if ($tableType === 1) {
            $typeFilter = ' AND t.table_type = 1 ';
        } else if ($tableType === 2) {
            $typeFilter = ' AND t.table_type = 2 ';
        }
        $schoolFilter = '';
        $sql1 = "SELECT DISTINCT tb.id AS id, tb.name AS name,1 as table_type
                 FROM fa_course_table_autoplan t
                 LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id
                 LEFT JOIN eb_classes c ON c.id = t.classes_id
                 left join fa_course_table tb on tb.id=t.table_id
                 WHERE t.status = 1 AND t.table_type = 1 AND $uid in (ct.teacher_id)";
        if ($schoolId > 0) {
            $sql1 .= " AND c.school_id = {$schoolId}";
        }
        $sql2 = "SELECT DISTINCT tb.id AS id, tb.project_name AS name,2 as table_type
                 FROM fa_course_table_autoplan t
                 LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id
                 LEFT JOIN fa_ocation_class oc ON oc.id = t.classes_id
                 left join fa_community_publicwelfare tb on tb.id=t.publicwelfare_id
                 WHERE t.status = 1 AND t.table_type = 2 AND $uid in (ct.teacher_id)";
        $rows = [];
        try {
            if ($schoolId > 0) { $params['school_id'] = $schoolId; }
            if ($tableType === 2) {
                $rows = Db::query($sql2);
            } else if ($tableType === 1) {
                $rows = Db::query($sql1);
            } else {
                $a = Db::query($sql1);
                log_info($sql1);
                $b = Db::query($sql2);
                log_info($sql2);
                $rows = array_merge($a ?: [], $b ?: []);
            }
        } catch (\Exception $e) {
            $rows = [];
        }
        $seen = [];
        $options = [];
        foreach ($rows as $r) {
            $id = intval($r['id']);
            $name = (string)$r['name'];
            if ($id > 0 && !isset($seen[$id])) {
                $options[] = ['label' => $name, 'value' => $id, 'table_type' => intval($r['table_type'])];
                $seen[$id] = true;
            }
        }
        usort($options, function($a, $b){ return strcmp($a['label'], $b['label']); });
        return json(['code' => 200, 'msg' => 'success', 'data' => ['options' => $options]]);
    }
    public function completionStatus(Request $request)
    {
        $user = $this->auth->getUser(true);
        $uid = isset($user['uid']) ? $user['uid'] : intval($request->get('user_id', 0));
        $tableType = intval($request->get('table_type', 0));
        $schoolId = intval($request->get('school_id', 0));
        $projectId = intval($request->get('project_id', 0));
        if ($uid <= 0) {
            return json(['code' => 0, 'msg' => '缺少用户身份']);
        }
        $typeFilter = '';
        if ($tableType === 1) {
            $typeFilter = ' AND t.table_type = 1 ';
        } else if ($tableType === 2) {
            $typeFilter = ' AND t.table_type = 2 ';
        }
        $schoolFilter = '';
        if ($schoolId > 0) {
            $schoolFilter = ' AND c.school_id = :school_id ';
        }
        $projectFilter = '';
        if ($projectId > 0) {
            if ($tableType === 1) {
                $projectFilter = ' AND t.table_id = :project_id ';
            } else if ($tableType === 2) {
                $projectFilter = ' AND t.publicwelfare_id = :project_id ';
            }
        }
        $sql = "SELECT t.id, t.classes_id, t.ocation_id, t.date, t.table_type, o.name AS ocation_name
                FROM fa_course_table_autoplan t
                LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id
                LEFT JOIN eb_classes c ON c.id = t.classes_id
                LEFT JOIN fa_ocation o ON o.id = t.ocation_id
                WHERE t.status = 1 {$typeFilter} {$schoolFilter} {$projectFilter}";// AND :uid in (ct.teacher_id)
        //$params = ['uid' => $uid];
        $params=[];
        if ($schoolId > 0) { $params['school_id'] = $schoolId; }
        if ($projectId > 0) { $params['project_id'] = $projectId; }
        try {
            $rows = Db::query($sql, $params);
        } catch (\Exception $e) {
            $rows = [];
        }
        $xKeys = [];
        $yKeys = [];
        $yLabels = [];
        $points = [];
        $now = time();
        foreach ($rows as $r) {
            $d = is_string($r['date']) ? strtotime($r['date']) : intval($r['date']);
            $dateMonthKey = date('Y-m', $d);
            $xKey = $dateMonthKey;
            $yKey = intval($r['ocation_id']);
            $yKeys[$yKey] = true;
            $yLabels[$yKey] = isset($r['ocation_name']) ? (string)$r['ocation_name'] : (string)$yKey;
            $xKeys[$xKey] = true;
            $points[] = [
                'x' => $xKey,
                'y' => $yKey,
                'classes_id' => intval($r['classes_id']),
                'date' => is_string($r['date']) ? $r['date'] : date('Y-m-d', $d),
                'ocation_id' => $yKey,
                'ocation_name' => isset($r['ocation_name']) ? (string)$r['ocation_name'] : '',
                'clickable' => ($tableType === 1),
                'completed' => ($d <= $now)
            ];
        }
        $xList = array_keys($xKeys);
        usort($xList, function($a, $b){ return strcmp((string)$a, (string)$b); });
        $xAxis = [];
        $xIndexMap = [];
        $idx = 1;
        foreach ($xList as $k) {
            $xIndexMap[$k] = $idx;
            $xAxis[] = ['index' => $idx, 'key' => $k, 'label' => (string)$k];
            $idx++;
        }
        $yList = array_keys($yKeys);
        sort($yList, SORT_NUMERIC);
        $yAxis = [];
        $yIndexMap = [];
        $idy = 1;
        foreach ($yList as $yk) {
            $yIndexMap[intval($yk)] = $idy;
            $yAxis[] = ['index' => $idy, 'key' => intval($yk), 'label' => $yLabels[$yk]];
            $idy++;
        }
        $points2 = [];
        foreach ($points as $p) {
            $xi = isset($xIndexMap[$p['x']]) ? $xIndexMap[$p['x']] : 0;
            $yi = isset($yIndexMap[$p['y']]) ? $yIndexMap[$p['y']] : 0;
            $points2[] = [
                'xi' => $xi,
                'yi' => $yi,
                'classes_id' => $p['classes_id'],
                'date' => $p['date'],
                'ocation_id' => $p['ocation_id'],
                'ocation_name' => $p['ocation_name'],
                'clickable' => $p['clickable'],
                'completed' => $p['completed']
            ];
        }
        $total = count($rows);
        $completed = 0;
        foreach ($points2 as $p) { if ($p['completed']) { $completed++; } }
        return json([
            'code' => 200,
            'msg' => 'success',
            'data' => [
                'axes' => ['x' => $xAxis, 'y' => $yAxis],
                'points' => $points2,
                'summary' => ['completed' => $completed, 'total' => $total]
            ]
        ]);
    }

    public function pointDetail(Request $request)
    {
        $user = $this->auth->getUser(true);
        $uid = isset($user['uid']) ? $user['uid'] : intval($request->get('user_id', 0));
        $classesId = intval($request->get('classes_id', 0));
        $ocationId = intval($request->get('ocation_id', 0));
        $dateStr = (string)$request->get('date', '');
        if ($uid <= 0) {
            return json(['code' => 0, 'msg' => '缺少用户身份']);
        }
        $filter = '';
        $params = [];
        if ($classesId > 0) { $filter .= ' AND t.classes_id = :classes_id '; $params['classes_id'] = $classesId; }
        if ($ocationId > 0) { $filter .= ' AND t.ocation_id = :ocation_id '; $params['ocation_id'] = $ocationId; }
        if ($dateStr !== '') {
            if (strlen($dateStr) === 7) {
                $filter .= " AND DATE_FORMAT(t.date,'%Y-%m') = :month ";
                $params['month'] = $dateStr;
            } else {
                $filter .= ' AND t.date LIKE :dateprefix ';
                $params['dateprefix'] = $dateStr.'%';
            }
        }
        $sql = "SELECT t.id, t.date, t.classes_id, t.ocation_id, c.name AS class_name, o.name AS ocation_name, s.name AS school_name,o.address AS address
                FROM fa_course_table_autoplan t
                LEFT JOIN eb_class_teacher ct ON ct.classes_id = t.classes_id
                LEFT JOIN eb_classes c ON c.id = t.classes_id
                LEFT JOIN eb_school s ON s.id = c.school_id
                LEFT JOIN fa_ocation o ON o.id = t.ocation_id
                WHERE t.status = 1 {$filter} 
                ORDER BY t.date ASC LIMIT 100";
        try {
            $rows = Db::query($sql, $params);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '查询失败']);
        }
        $list = [];
        foreach ($rows as $r) {
            $d = is_string($r['date']) ? strtotime($r['date']) : intval($r['date']);
            $list[] = [
                'date' => is_string($r['date']) ? $r['date'] : date('Y-m-d', $d),
                'classes_id' => intval($r['classes_id']),
                'class_name' => (string)$r['class_name'],
                'ocation_id' => intval($r['ocation_id']),
                'ocation_name' => (string)$r['ocation_name'],
                'school_name' => (string)$r['school_name'],
                'address' => (string)$r['address']
            ];
        }
        return json(['code' => 200, 'msg' => 'success', 'data' => ['list' => $list]]);
    }
    public function correspondenceList(Request $request)
    {
        file_put_contents($_SERVER['DOCUMENT_ROOT'].'/../runtime/api.log', 'ProjectCourse/correspondenceList '.json_encode($_GET).PHP_EOL, FILE_APPEND);
        $classesId = intval($request->get('classes_id', 0));
        $date = trim($request->get('date', ''));

        $q = Db::name('fa_course_table_autoplan')->alias('t')
            ->join('fa_ocation o', 'o.id=t.ocation_id','left')
            ->field('t.classes_id,t.ocation_id,t.date,o.name as ocation_name,o.address as address');

        if ($classesId > 0) $q->where('t.classes_id', $classesId);
        if ($date !== '') {
            $y = substr($date, 0, 4);
            $q->whereLike('t.date', "{$y}%");
        }

        $raw = $q->order('t.date asc,t.id asc')->limit(500)->select();
        $list = [];
        $selectedIndex = -1;
        foreach ($raw as $i => $r) {
            $ts = strtotime($r['date']);
            $label = $ts ? date('Y年n月', $ts) : $r['date'];
            $monthNum = $ts ? intval(date('n', $ts)) : 0;
            $row = [
                'month_num' => $monthNum,
                'label' => $label,
                'ocation_name' => $r['ocation_name'],
                'classes_id' => intval($r['classes_id']),
                'ocation_id' => intval($r['ocation_id']),
                'date' => $r['date']
            ];
            if ($date !== '' && $r['date'] === $date) $selectedIndex = $i;
            $list[] = $row;
        }

        return json(['code' => 200, 'data' => ['list' => $list, 'selectedIndex' => $selectedIndex], 'msg' => 'ok']);
    }


}

